import numpy as np
import pandas as pd
import numpy_financial as npf  # 确保已安装 numpy-financial

# 参数设置
annual_rate = 0.05
monthly_rate = annual_rate / 12
monthly_invest = 10000
target_age = 65

# 计算未来值的函数
def calculate_wealth(start_age):
    years = target_age - start_age
    months = years * 12
    fv = npf.fv(monthly_rate, months, -monthly_invest, 0)
    return round(fv, 2)

# 计算需要追加的金额
def calculate_additional(start_age, target_fv):
    years = target_age - start_age
    months = years * 12
    needed_pmt = -npf.pmt(monthly_rate, months, 0, target_fv)
    additional = max(0, needed_pmt - monthly_invest)
    return round(additional, 2)

# 计算每个人的基础数据
persons = [
    {"name": "A", "start": 25},
    {"name": "B", "start": 28},
    {"name": "C", "start": 30}
]

# 计算A的最终资产
a_fv = calculate_wealth(25)

# 构建结果数据集
results = []
for p in persons:
    name = p["name"]
    start = p["start"]
    years = target_age - start

    # 原始投资结果
    original_fv = calculate_wealth(start)

    # 需要追加的金额
    additional = calculate_additional(start, a_fv) if name != "A" else 0

    results.append({
        "姓名": name,
        "开始年龄": start,
        "投资年限": years,
        "最终年龄": target_age,
        "每月定投(元)": monthly_invest,
        "最终资产(元)": original_fv,
        "需追加金额(元)": additional,
        "追加后月投(元)": monthly_invest + additional if name != "A" else monthly_invest
    })

# 创建DataFrame并保存到Excel
df = pd.DataFrame(results)
df = df[["姓名", "开始年龄", "投资年限", "最终年龄", "每月定投(元)", "需追加金额(元)", "追加后月投(元)", "最终资产(元)"]]

# 格式化显示金额
format_dict = {
    "最终资产(元)": "{:,.2f}",
    "每月定投(元)": "{:,.0f}",
    "需追加金额(元)": "{:,.0f}",
    "追加后月投(元)": "{:,.0f}"
}

# 修正格式化逻辑
for col, fmt in format_dict.items():
    df[col] = df[col].apply(lambda x: fmt.format(x))

# 保存到Excel
df.to_excel("投资模拟结果.xlsx", index=False)

print("计算结果已保存到 投资模拟结果.xlsx")